/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import pojo.Cuahang;
import pojo.Doanhthu;
import util.HibernateUtil;

/**
 *
 * @author Cong
 */
public class daoDoanhThu {

    public static List<Cuahang> laydsCuaHang() throws HibernateException {
        List<Cuahang> ds = null;
        Session sS = HibernateUtil.getSessionFactory().openSession();
        String sql = "from Cuahang";
        try {
            Query query = sS.createQuery(sql);
            ds = query.list();
        } catch (HibernateException he) {
            System.err.println(he);
        } finally {
            sS.close();
        }
        return ds;
    }

    public static List<Doanhthu> laydsDoanhThulimit() throws HibernateException {
        List<Doanhthu> ds = null;
        String sql = "FROM Doanhthu WHERE loai=4 order by id asc";
        Session sS = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = sS.createQuery(sql);
            ds = query.list();
        } catch (HibernateException he) {
            System.err.println(he);
        } finally {
            sS.close();
        }
        return ds;
    }

    public static List<Doanhthu> laydsDoanhThuNgay(Date ngay, Cuahang cuahang) {
        List<Doanhthu> ds = null;
        SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
        String sql = "FROM Doanhthu WHERE loai=1 AND cuahang=:cuahang AND ngay=:ngay order by id asc";
        Session sS = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = sS.createQuery(sql);
            query.setParameter("cuahang",cuahang);
            query.setDate("ngay",ngay);
            ds = query.list();
        } catch (HibernateException he) {
            System.err.println(he);
        } finally {
            sS.close();
        }
        return ds;
    }
    
    public static List<Doanhthu> laydsDoanhThuThang(Date ngay, Cuahang cuahang) {
        List<Doanhthu> ds = null;
        String sql = "FROM Doanhthu WHERE loai=2 AND cuahang=:cuahang AND DATE_PART('month',ngay)=:month AND DATE_PART('year',ngay)=:year order by id asc";
        Calendar cl = Calendar.getInstance();
        cl.setTime(ngay);
        Session sS = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = sS.createQuery(sql);
            query.setParameter("cuahang", cuahang);
            query.setInteger("month", cl.get(Calendar.MONTH) + 1);
            query.setInteger("year", cl.get(Calendar.YEAR));
            ds = query.list();
        } catch (HibernateException he) {
            System.err.println(he);
        } finally {
            sS.close();
        }
        return ds;
    }
    
    public static List<Doanhthu> laydsDoanhThuQuy(Date ngay, Cuahang cuahang) {
        List<Doanhthu> ds = null;
        String sql = "FROM Doanhthu WHERE loai=3 AND cuahang=:cuahang AND DATE_PART('month',ngay)=:month AND DATE_PART('year',ngay)=:year order by id asc";
        Calendar cl = Calendar.getInstance();
        cl.setTime(ngay);
        int month = cl.get(Calendar.MONTH)/3;
        month = month * 3 + 1;
        Session sS = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = sS.createQuery(sql);
            query.setParameter("cuahang", cuahang);
            query.setInteger("month", month);
            query.setInteger("year", cl.get(Calendar.YEAR));
            ds = query.list();
        } catch (HibernateException he) {
            System.err.println(he);
        } finally {
            sS.close();
        }
        return ds;
    }
    
    public static List<Doanhthu> laydsDoanhThuNam(Date ngay, Cuahang cuahang) {
        List<Doanhthu> ds = null;
        String sql = "FROM Doanhthu WHERE loai=4 AND cuahang=:cuahang AND DATE_PART('year',ngay)=:year order by id asc";
        Calendar cl = Calendar.getInstance();
        cl.setTime(ngay);
        Session sS = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = sS.createQuery(sql);
            query.setInteger("year", cl.get(Calendar.YEAR));
            query.setParameter("cuahang", cuahang);
            ds = query.list();
        } catch (HibernateException he) {
            System.err.println(he);
        } finally {
            sS.close();
        }
        return ds;
    }
}
